﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
using Skynet.BusinessEntities;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using L2S =Skynet.BusinessLogic.DataAccess.SkynetLibraryL2S;
using ExamCategoryInternal = Skynet.BusinessLogic.DataAccess.SkynetLibraryL2S.ExamCategory;
namespace Skynet.BusinessLogic.DataAccess
{
    
    public class ExamCategoryL2SRepository :  IExamCategoryRepository
    {        
        //private string ConnectionString = null;
        private SqlConnection ConnectionString = null; 
        public ExamCategoryL2SRepository(string ConnectionName)
        {

            ConnectionString = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString);
            ConnectionString.Open();
            //ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;
        }

        private L2S.ExamCategory ConvertFrom(BusinessEntities.ExamCategory exam)
        {
            if (exam == null) return null; 
            var result = new ExamCategoryInternal();
            result.Id = exam.Id;
            result.Title = exam.Title;
            result.Description = exam.Description;  
            return result; 
        }
        private BusinessEntities.ExamCategory ConvertTo(ExamCategoryInternal exam)
        {
            if (exam == null) return null; 
            var result = new ExamCategory();
            result.Id = exam.Id;
            result.Title = exam.Title;
            result.Description = exam.Description;
            return result; 
        }

        public void Add(BusinessEntities.ExamCategory item)
        {
            try
            {
                DataContext context = new DataContext(this.ConnectionString);                
                var ExamCategories = context.GetTable<ExamCategoryInternal>();
                ExamCategories.InsertOnSubmit(ConvertFrom(item));
                context.SubmitChanges();
                context.Dispose();                
                
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            
        }

        public void Add(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            var context = new L2S.SkynetLibraryL2SDataContext(this.ConnectionString);
            var ExamCategories = context.GetTable<ExamCategoryInternal>();
            
            try
            {
                if (context.Connection.State != ConnectionState.Open)
                    context.Connection.Open();
                context.Transaction = context.Connection.BeginTransaction();

                
                foreach (var item in items)
                {
                    context.usp_ExamCategories_Insert(item.Id, item.Title, item.Description);
                    //ExamCategories.InsertOnSubmit(ConvertFrom(item));

                }
                context.SubmitChanges();
                foreach (var item in items)
                {
                    item.AcceptChanges();
                }
                context.Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (context.Transaction != null)
                    context.Transaction.Rollback();
            }
            finally { 

            }
        }

        public void Update(BusinessEntities.ExamCategory item)
        {
            DataContext context = new DataContext(this.ConnectionString);
            try 
	        {
                
                var ExamCategories = context.GetTable<ExamCategoryInternal>();
                var target = (from c in ExamCategories where c.Id == item.Id select c).SingleOrDefault();
                if (target != null)
                {
                    target.Title = item.Title;
                    target.Description = item.Description;
                    context.SubmitChanges();
                }                   
	        }
            catch (ChangeConflictException )
	        {
                //MetaTable metatable = db.Mapping.GetTable(occ.Object.GetType());
                //Customer entityInConflict = (Customer)occ.Object;
                //Console.WriteLine("Table name: {0}", metatable.TableName);
                //Console.Write("Customer ID: ");
                //Console.WriteLine(entityInConflict.CustomerID);
                //foreach (MemberChangeConflict mcc in occ.MemberConflicts)
                //{
                //    object currVal = mcc.CurrentValue;
                //    object origVal = mcc.OriginalValue;
                //    object databaseVal = mcc.DatabaseValue;
                //    MemberInfo mi = mcc.Member;
                //    Console.WriteLine("Member: {0}", mi.Name);
                //    Console.WriteLine("current value: {0}", currVal);
                //    Console.WriteLine("original value: {0}", origVal);
                //    Console.WriteLine("database value: {0}", databaseVal);
                //    Console.ReadLine();
                //}
                foreach (ObjectChangeConflict occ in context.ChangeConflicts)
                {
                    occ.Resolve(RefreshMode.OverwriteCurrentValues);
                }
		        
	        }
            
    
        }

        public void Update(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            DataContext context = new DataContext(this.ConnectionString);
            var ExamCategories = context.GetTable<ExamCategoryInternal>();
            foreach (var item in items)
            {
                var target = (from c in ExamCategories where c.Id == item.Id select c).SingleOrDefault();
                if (target != null)
                {
                    target.Title = item.Title;
                    target.Description = item.Description;
                    
                }                   
            }
            context.SubmitChanges();
                            
        }

        public void Delete(BusinessEntities.ExamCategory item)
        {
            DataContext context = new DataContext(this.ConnectionString);
            var ExamCategories = context.GetTable<ExamCategoryInternal>();
            var target = (from c in ExamCategories where c.Id == item.Id select c).SingleOrDefault();
            if (target != null)
            {
                ExamCategories.DeleteOnSubmit(target); 
                context.SubmitChanges();
            }                   
        }

        public void Delete(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            DataContext context = new DataContext(this.ConnectionString);
            var ExamCategories = context.GetTable<ExamCategoryInternal>();
            foreach (var item in items)
            {
                var target = (from c in ExamCategories where c.Id == item.Id select c).SingleOrDefault();
                if (target != null)
                {
                    ExamCategories.DeleteOnSubmit(target);     

                }
            }
            context.SubmitChanges();
        }

        public BusinessEntities.ExamCategory GetOne(object key)
        {
            DataContext context = new DataContext(this.ConnectionString);
            var ExamCategories = context.GetTable<ExamCategoryInternal>();
            var target = (from c in ExamCategories where c.Id == (Guid)key select c).SingleOrDefault();
            return ConvertTo(target); 
        }

        public IEnumerable<BusinessEntities.ExamCategory> GetAll()
        {
            List<BusinessEntities.ExamCategory> result = new List<ExamCategory>();
            var context = new L2S.SkynetLibraryL2SDataContext(this.ConnectionString);                        
            var ExamCategories = context.GetTable<ExamCategoryInternal>();
            var target = (from c in ExamCategories select c).ToList();
            foreach (var item in target)
            {
                result.Add(ConvertTo(item));
                var exams = item.Exams;
            }
            results.Add(target);
            ///context.Dispose();            
            return result; 
        }
        protected List<List<ExamCategoryInternal>> results = new List<List<ExamCategoryInternal>>();  

        public System.Data.IDataReader GetAllStream()
        {
            throw new NotImplementedException();
            //SqlCommand cmd = null;
            //SqlDataReader reader = null;
            //SqlConnection Connection = null;
            //try
            //{
            //    Connection = new SqlConnection(this.ConnectionString.ConnectionString);
            //    cmd = new SqlCommand("select * from ExamCategories", Connection);
            //    Connection.Open();
            //    reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult | System.Data.CommandBehavior.CloseConnection);
            //}
            //catch (Exception ex)
            //{
            //    bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
            //    if (rethrow)
            //        throw;
            //}
            //return reader;  
        }
        public System.Data.DataSet GetTable()
        {
            throw new NotImplementedException();
            //SqlCommand cmd = null;
            //DataSet dataset = new DataSet();
            //SqlConnection Connection = null;
            //try
            //{
            //    Connection = new SqlConnection(this.ConnectionString.ConnectionString);
            //    cmd = new SqlCommand("select * from ExamCategories", Connection);
            //    SqlDataAdapter adp = new SqlDataAdapter(cmd);
            //    adp.Fill(dataset, "ExamCategories");
            //}
            //catch (Exception ex)
            //{
            //    bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
            //    if (rethrow)
            //        throw;
            //}
            //return dataset;
        }


        DataTable IExamCategoryRepository.GetTable()
        {
            throw new NotImplementedException();
        }

        public void UpdateTable(DataTable ds)
        {
            throw new NotImplementedException();
        }


        public void Maintenance(ExamCategory item)
        {
            throw new NotImplementedException();
        }
    }
}
